﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DTO;
using System.Threading;

namespace DAO
{
    public class DonHangDAO
    {
		public static List<DonHangDTO> DanhSachDonHang(string LoaiDonHang, string TinhTrang, DateTime NgayMua, int MaKhachHang, ref int dem, string run)
        {

            IsolationLevel iso;
            switch (run)
            {
                case "Phantom error":
                    iso = IsolationLevel.ReadCommitted;
                    break;
                case "Phantom fix":
                    iso = IsolationLevel.Serializable;
                    break;
                default:
                    iso = IsolationLevel.ReadCommitted;
                    break;
            }
                //usp_DanhSachDonHang ,@LoaiDonHang varchar(3)=null,@TinhTrang nvarchar(20)=null, 
	            //@NgayMua datetime=null, @MaKhachHang int=null
            SqlConnection connect = new SqlConnection(DataProvider.ConnectionString);
            string sqlCount = "select count(dh.MaDonHang) from DonHang dh ";
            string sqlSelect = "select dh.* from DonHang dh ";
            string sqlWhere = "";
            string temp = "";
            List<string> lstString = new List<string>();

            if (LoaiDonHang != "")
            {
                temp = "  (dh.MaDonHang like '" + LoaiDonHang + "%' ) ";
                lstString.Add(temp);
            }
            if (TinhTrang != "")
            {
                temp = "  (dh.TinhTrang like N'" + TinhTrang + "') ";
                lstString.Add(temp);
            }
            if (NgayMua != DateTime.MinValue)
            {
                temp = "  (datediff(dd,dh.NgayMua,'" + NgayMua + "') = 0  ) ";
                lstString.Add(temp);
            }
            if (MaKhachHang != -1)
            {
                temp = "  (dh.MaKhachHang = "+MaKhachHang+") ";
                lstString.Add(temp);
            }

            if (lstString.Count > 0)
            {
                sqlWhere = " where " + lstString[0];
                for (int i = 1; i < lstString.Count; i++)
                {
                    sqlWhere = sqlWhere + " and " + lstString[i];
                }
            }
            sqlCount += sqlWhere;
            sqlSelect += sqlWhere;

            connect.Open();
            SqlTransaction tran = connect.BeginTransaction(iso);
            SqlCommand command = new SqlCommand(sqlCount, connect, tran);
            dem = (int)command.ExecuteScalar();//lấy số lượng kết quả
            command = new SqlCommand(sqlSelect, connect, tran);

            if (run == "Phantom error" || run == "Phantom fix")
            {
                Thread.Sleep(10000);
            }

           // command.ExecuteNonQuery();
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            tran.Commit();
            connect.Close();

            List<DonHangDTO> listDonHangDTO = new List<DonHangDTO>();
            if (dataTable.Rows.Count > 0)
            {
                foreach (DataRow dataRow in dataTable.Rows)
                {
                    DonHangDTO donHangDTO = new DonHangDTO();
                    donHangDTO.MaDonHang = (dataRow["MaDonHang"].ToString());
                    donHangDTO.NgayMua = DateTime.Parse(dataRow["NgayMua"].ToString());
                    donHangDTO.NgayHetHan = DateTime.Parse(dataRow["NgayHetHan"].ToString());
                    donHangDTO.ThanhTien = float.Parse(dataRow["ThanhTien"].ToString());
                    donHangDTO.MaKhachHang = int.Parse(dataRow["MaKhachHang"].ToString());
                    donHangDTO.TinhTrang = dataRow["TinhTrang"].ToString();
                    listDonHangDTO.Add(donHangDTO);
                }
            }
            else
            {
                listDonHangDTO = null;
            }
            return listDonHangDTO;
            
        }

        public static DonHangDTO ThongTinDonHang(string maDonHang, string run)
        {
            IsolationLevel iso;
            if (run == "Dirty read error")
            {
                iso = IsolationLevel.ReadUncommitted;
            }
            else
            {
                iso = IsolationLevel.ReadCommitted;
            }
            //usp_ThongTinDonHang 	@MaDH char(17)
            SqlConnection connect = new SqlConnection(DataProvider.ConnectionString);
            string sqlSelect = "select dh.* from DonHang dh where dh.MaDonHang = '"+ maDonHang+"'";
            //mở kết nối
            connect.Open();
            //mở transaction
            SqlTransaction tran = connect.BeginTransaction(iso);

            SqlCommand cmd = new SqlCommand(sqlSelect, connect, tran);
           // cmd.ExecuteNonQuery();
            
            //hứng kết quả
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            DonHangDTO donHangDTO = new DonHangDTO();
            
            if (dataTable.Rows.Count > 0)
            {
                DataRow dataRow = dataTable.Rows[0];
                int masanpham = int.Parse(dataRow["MaSanPham"].ToString());
                sqlSelect = "select * from Sim where MaSanPham = " + masanpham;
                cmd = new SqlCommand(sqlSelect, connect, tran);
                adapter = new SqlDataAdapter(cmd);
                dataTable = new DataTable();
                adapter.Fill(dataTable);
                if (dataTable.Rows.Count > 0)
                {
                    sqlSelect = "select * from DonHang dh join Sim si on dh.MaSanPham = si.MaSanPham  where dh.MaDonHang ='" + maDonHang+"'";
                    cmd = new SqlCommand(sqlSelect, connect, tran);
                    adapter = new SqlDataAdapter(cmd);
                    dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    if (dataTable.Rows.Count > 0)
                    {
                        DataRow dtRow = dataTable.Rows[0];
                        donHangDTO = new DonHangDTO();
                        donHangDTO.MaKhachHang = int.Parse(dtRow["MaKhachHang"].ToString());
                        donHangDTO.MaSanPham = int.Parse(dtRow["MaSanPham"].ToString());
                        donHangDTO.NgayMua = DateTime.Parse(dtRow["NgayMua"].ToString());
                        donHangDTO.NgayHetHan = DateTime.Parse(dtRow["NgayHetHan"].ToString());
                        donHangDTO.ThanhTien = float.Parse(dtRow["ThanhTien"].ToString());
                        donHangDTO.TinhTrang = dtRow["TinhTrang"].ToString();
                    }
                }
                else
                {
                    sqlSelect = "select * from DonHang dh join TheCao tc on dh.MaSanPham = tc.MaSanPham  where dh.MaDonHang = '" + maDonHang+"'";
                    cmd = new SqlCommand(sqlSelect, connect, tran);
                    adapter = new SqlDataAdapter(cmd);
                    dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    if (dataTable.Rows.Count > 0)
                    {
                        DataRow dtRow = dataTable.Rows[0];
                        donHangDTO = new DonHangDTO();
                        donHangDTO.MaKhachHang = int.Parse(dtRow["MaKhachHang"].ToString());
                        donHangDTO.MaSanPham = int.Parse(dtRow["MaSanPham"].ToString());
                        donHangDTO.NgayMua = DateTime.Parse(dtRow["NgayMua"].ToString());
                        donHangDTO.NgayHetHan = DateTime.Parse(dtRow["NgayHetHan"].ToString());
                        donHangDTO.ThanhTien = float.Parse(dtRow["ThanhTien"].ToString());
                        donHangDTO.TinhTrang = dtRow["TinhTrang"].ToString();
                    }
                }
            }
            else
            {
                donHangDTO = null;
            }
            ////thực thi tran
            tran.Commit();
            ////đóng kết nối
            connect.Close();
            return donHangDTO;
        }

        public static bool CapNhatDonHang(string MaDonHang, string TinhTrang, string run)
        {
            //usp_CapNhatDonHang 	@MaDH char(17), 
	        //@TinhTrang nvarchar(20)

            IsolationLevel iso;
            if (run == "Lost update fix")
            {
                iso = IsolationLevel.Serializable;
            }
            else
            {
                iso = IsolationLevel.ReadCommitted;
            }

            SqlConnection connect = new SqlConnection(DataProvider.ConnectionString);
            string sqlSelect ;
            string sqlInsert ;
            string sqlUpdate ;
            int kq1 = -1;
            int kq2 = -1;
            int kq3 = -1;
            //mở kết nối
            connect.Open();
            //mở transaction
            SqlTransaction tran = connect.BeginTransaction(iso);


            if (run == "Lost update fix")
            {
                sqlSelect = "select dh.* from DonHang dh with (updlock)  where dh.MaDonHang= '" + MaDonHang + "' and TinhTrang like N'Chờ'";
            }
            else
            {
                sqlSelect = "select dh.* from DonHang dh where dh.MaDonHang= '" + MaDonHang + "' and TinhTrang like N'Chờ'";
            }
           
            SqlCommand cmdupdate;
            SqlCommand cmdselect;
            cmdselect = new SqlCommand(sqlSelect, connect, tran);
            //hứng kết quả
            SqlDataAdapter adapter = new SqlDataAdapter(cmdselect);
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            if (dataTable.Rows.Count > 0)//kiem tra ton tai don hang tren
            {
                if (run == "Lost update error" || run == "Lost update fix")
                {
                    Thread.Sleep(10000);
                }

                sqlUpdate = "update DonHang set TinhTrang= N'" + TinhTrang + "' where MaDonHang= '" + MaDonHang + "'";
                cmdupdate = new SqlCommand(sqlUpdate, connect, tran);
                kq1 = cmdupdate.ExecuteNonQuery();
                if (kq1 <= 0)
                {
                    tran.Rollback();
                    return false;
                }


                if (run == "Dirty read error" || run == "Dirty read fix")
                {
                    Thread.Sleep(10000);
                }

                //cap nhat nhung bang lien quan: SanPham , Sim, TheCao, KhachHang trong QD4 & QD5
                DateTime NgayHienHanh = DateTime.Now;
                int MaSanPham = -1;
                int MaKhachHang = -1;
                DateTime NgayHetHan = DateTime.Now;
                //select @MaSP = MaSanPham, @NgayHH = NgayHetHan, @MaKH = MaKhachHang from DonHang where MaDonHang=@MaDH
                sqlSelect = "select dh.MaSanPham, dh.NgayHetHan, dh.MaKhachHang from DonHang dh where dh.MaDonHang= '" + MaDonHang + "'";
                cmdselect = new SqlCommand(sqlSelect, connect, tran);
                //hứng kết quả
                adapter = new SqlDataAdapter(cmdselect);
                dataTable = new DataTable();
                adapter.Fill(dataTable);
                if (dataTable.Rows.Count > 0)
                {
                    DataRow dataRow = dataTable.Rows[0];
                    MaSanPham = int.Parse(dataRow["MaSanPham"].ToString());
                    MaKhachHang = int.Parse(dataRow["MaKhachHang"].ToString());
                    NgayHetHan = DateTime.Parse(dataRow["NgayHetHan"].ToString());
                }
                //tinh trang huy thi cap nhat san pham la "dang ban":
                if (TinhTrang == "Hủy")
                {
                    sqlUpdate = "update SanPham set TinhTrang= N'Đang Bán' where MaSanPham =" + MaSanPham;
                    cmdupdate = new SqlCommand(sqlUpdate, connect, tran);
                    kq2 = cmdupdate.ExecuteNonQuery();
                    if (kq2 <= 0)
                    {
                        tran.Rollback();
                        return false;
                    }
                }
                //kiem tra QD4 va QD5:
                sqlSelect = "Select * from Sim where MaSanPham=" + MaSanPham;
                cmdselect = new SqlCommand(sqlSelect, connect, tran);
                adapter = new SqlDataAdapter(cmdselect);
                dataTable = new DataTable();
                adapter.Fill(dataTable);
                if (dataTable.Rows.Count > 0)//neu la SIM
                {
                    if (DateTime.Compare(NgayHienHanh, NgayHetHan) < 0 && TinhTrang == "Thành Công")
                    {
                        tran.Rollback();
                        return false;
                    }

                    if (DateTime.Compare(NgayHienHanh, NgayHetHan) > 0 && TinhTrang == "Hủy")
                    {
                        tran.Rollback();
                        return false;
                    }
                }
                else//neu la THECAO
                {
                    if (DateTime.Compare(NgayHienHanh, NgayHetHan) > 0 && TinhTrang == "Thành Công")
                    {
                        tran.Rollback();
                        return false;
                    }
                }
                //kiem tra so luong de block tai khoan khach hang:
                sqlSelect = "select count(*) from (select top(3)hd.TinhTrang from" +
                                                    " KhachHang kh join DonHang hd on kh.MaKhachHang = hd.MaKhachHang where kh.MaKhachHang = " + MaKhachHang +
                                                    " order by hd.NgayMua desc) as KH where KH.TinhTrang like N'Hủy'";
                cmdselect = new SqlCommand(sqlSelect, connect, tran);
                int soluong = (int)cmdselect.ExecuteScalar();
                if (soluong >= 3)
                {
                    sqlUpdate = "update KhachHang set TinhTrang=N'Khóa'	where MaKhachHang = " + MaKhachHang;
                    cmdupdate = new SqlCommand(sqlUpdate, connect, tran);
                    kq3 = cmdupdate.ExecuteNonQuery();
                    if (kq3 <= 0)
                    {
                        tran.Rollback();
                        return false;
                    }
                }
            }
            else
            {
                tran.Rollback();
                return false;
            }
            //thực thi tran
            tran.Commit();
            //đóng kết nối
            connect.Close();
            return true;
        }
        
        public static bool MuaSanPham(int masanpham, int makhachhang)
        {
            //usp_MuaSanPham @masanpham int, @makhachhang int
            SqlConnection connect = new SqlConnection(DataProvider.ConnectionString);
            string sqlSelect;
            string sqlInsert;
            string sqlUpdate;
            string madonhang, tinhtrang;
            float thanhtien;
            DateTime ngaymua, ngayhethan;
            ngaymua = DateTime.Now;
            ngayhethan = ngaymua.AddDays(10);
            tinhtrang = "Chờ";

            //mở kết nối
            connect.Open();
            //mở transaction
            SqlTransaction tran = connect.BeginTransaction();
            sqlSelect = "select kh.* from KhachHang kh where kh.MaKhachHang= " + makhachhang;
            SqlCommand cmdselect = new SqlCommand(sqlSelect, connect, tran);
            //hứng kết quả
            SqlDataAdapter adapter = new SqlDataAdapter(cmdselect);
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            //kiem tra khach hang ton tai
            if (dataTable.Rows.Count <= 0)
            {
                tran.Rollback();
                return false;
            }

            sqlSelect = "select sp.* from SanPham sp where sp.TinhTrang = N'Đang Bán' and sp.MaSanPham= " + masanpham;
            cmdselect = new SqlCommand(sqlSelect, connect, tran);
            //hứng kết quả
            adapter = new SqlDataAdapter(cmdselect);
            dataTable = new DataTable();
            adapter.Fill(dataTable);
            //kiem tra san pham co "dang ban"
            if (!(dataTable.Rows.Count > 0))
            {
                tran.Rollback();
                return false;
            }
            DataRow dataRow = dataTable.Rows[0];
            thanhtien = float.Parse(dataRow["DonGia"].ToString());
            //declare @madonhang char(17)
            //declare @thanhtien float
            //declare @ngaymua datetime
            //declare @ngayhethan datetime
            //declare @tinhtrang nvarchar(20)
            //set @ngaymua = getdate()
            

            //kiem tra la sim hay the cao -> tao ma don hang
            sqlSelect = "select s.* from Sim s where s.MaSanPham = " +masanpham ;
            cmdselect = new SqlCommand(sqlSelect, connect, tran);
            adapter = new SqlDataAdapter(cmdselect);
            dataTable = new DataTable();
            adapter.Fill(dataTable);
            if (dataTable.Rows.Count > 0)
            {
                //day la sim
                madonhang = "SIM" + ngaymua.ToString("ddMMyyyyhhmmss");
            }
            else//day la the cao
            {
                madonhang = "CAR" + ngaymua.ToString("ddMMyyyyhhmmss");                
            }

            sqlInsert = "insert into DonHang(MaDonHang, MaSanPham, MaKhachHang, ThanhTien, NgayMua, NgayHetHan, TinhTrang) values(@madonhang,@masanpham,@makhachhang,@thanhtien,@ngaymua,@ngayhethan,@tinhtrang)";
           // sqlInsert = "insert into DonHang(MaDonHang, MaSanPham, MaKhachHang, ThanhTien, NgayMua, NgayHetHan, TinhTrang) values('"+madonhang+"',"+masanpham+","+makhachhang+","+thanhtien+",'"+ngaymua+"','"+ngayhethan+"',"+tinhtrang+")";
            SqlCommand cmdinsert = new SqlCommand(sqlInsert, connect, tran);
            cmdinsert.Parameters.Add("@madonhang", SqlDbType.Char, 17);
            cmdinsert.Parameters.Add("@masanpham", SqlDbType.Int);
            cmdinsert.Parameters.Add("@makhachhang", SqlDbType.Int);
            cmdinsert.Parameters.Add("@thanhtien", SqlDbType.Float);
            cmdinsert.Parameters.Add("@ngaymua", SqlDbType.DateTime);
            cmdinsert.Parameters.Add("@ngayhethan", SqlDbType.DateTime);
            cmdinsert.Parameters.Add("@tinhtrang", SqlDbType.NVarChar, 20);

            cmdinsert.Parameters["@madonhang"].Value = madonhang;
            cmdinsert.Parameters["@masanpham"].Value = masanpham;
            cmdinsert.Parameters["@makhachhang"].Value = makhachhang;
            cmdinsert.Parameters["@thanhtien"].Value = thanhtien;
            cmdinsert.Parameters["@ngaymua"].Value = ngaymua;
            cmdinsert.Parameters["@ngayhethan"].Value = ngayhethan;
            cmdinsert.Parameters["@tinhtrang"].Value = tinhtrang;
            int kq1= cmdinsert.ExecuteNonQuery();

            sqlUpdate = "update	SanPham set TinhTrang = N'Đã Bán'	where MaSanPham = "+masanpham;
            SqlCommand cmdupdate = new SqlCommand(sqlUpdate, connect, tran);
            int kq2 = cmdupdate.ExecuteNonQuery();
            tran.Commit();
            connect.Close();
            if (kq1 > 0 && kq2 > 0)
                return true;
            else
                return false;
        }

        public static int ThanhToan(string madonhang, string run)
        {
            IsolationLevel iso;
            if (run == "Lost update fix")
            {
                iso = IsolationLevel.Serializable;
            }
            else
            {
                iso = IsolationLevel.ReadCommitted;
            }
            //usp_ThanhToan @madonhang char(17)
            SqlConnection connect = new SqlConnection(DataProvider.ConnectionString);
            string sqlSelect;
            string sqlUpdate;
            connect.Open();
            SqlTransaction tran = connect.BeginTransaction(iso);

            if (run == "Lost update fix")
            {
                sqlSelect = "select dh.* from DonHang dh with (updlock) where  dh.MaDonHang = '" + madonhang + "' and dh.TinhTrang = N'Chờ'";
            }
            else
            {
                sqlSelect = "select dh.* from DonHang dh where  dh.MaDonHang = '" + madonhang + "' and dh.TinhTrang = N'Chờ'";
            }

          
            SqlCommand cmdselect = new SqlCommand(sqlSelect, connect, tran);
            SqlDataAdapter adapter = new SqlDataAdapter(cmdselect);
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            if (dataTable.Rows.Count <= 0)
            {
                //đơn hàng không tồn tại
                tran.Rollback();
                return 1;
            }

            sqlSelect = "select * from DonHang dh, TheCao tc where dh.MaDonHang = '" + madonhang + "' and dh.MaSanPham = tc.MaSanPham and datediff(D,getdate(),dh.NgayHetHan) >= 0";
            cmdselect = new SqlCommand(sqlSelect, connect, tran);
            adapter = new SqlDataAdapter(cmdselect);
            dataTable = new DataTable();
            adapter.Fill(dataTable);
            if (dataTable.Rows.Count <= 0)
            {
                //đơn hàng  không thể thanh toán
                tran.Rollback();
                return 2;
            }


            if (run == "Lost update error" || run == "Lost update fix")
            {
                Thread.Sleep(10000);
            }

            sqlUpdate = "update DonHang set TinhTrang = N'Thành công' where MaDonHang = '" + madonhang + "'";
            SqlCommand cmdupdate = new SqlCommand(sqlUpdate, connect, tran);
            if (cmdupdate.ExecuteNonQuery() <= 0)
            {
                //đơn hàng không thể thanh toán
                tran.Rollback();
                return 3;
            }
            tran.Commit();
            return 0;
        }
    }
}
